package com.wframe.project.former.dataservices.user;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import com.frame.database.JBaseDao;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;

public class UserDao extends JBaseDao {

    public UserDao() {
        super("wbuser");
    }

    public PageResult queryUser(JdataBean parms, int size, int pageno) throws Exception {

        String sql = "select u.*,c.name comname,c.idnum comidnum,c.type comtype,c.contacts,c.phone comphone"
                + " from wbuser u"
                + " left join wb_company c on c.userid=u.id"
                + " where u.state<>-1";
        List parmlist = new ArrayList();
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("like"))) {
                sql += " and (u.phone like ? or u.loginname like ? or u.nickname like ?)";
                parmlist.add("%" + parms.getString("like") + "%");
                parmlist.add("%" + parms.getString("like") + "%");
                parmlist.add("%" + parms.getString("like") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("phone"))) {
                sql += " and u.phone like ?";
                parmlist.add("%" + parms.getString("phone") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("contacts"))) {
                sql += " and c.contacts like ?";
                parmlist.add("%" + parms.getString("contacts") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("comname"))) {
                sql += " and c.name like ?";
                parmlist.add("%" + parms.getString("comname") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("start"))) {
                sql += " and u.regtime >=?";
                parmlist.add(parms.getString("start"));
            }
            if (StringUtils.isNotBlank(parms.getString("end"))) {
                sql += " and u.regtime <=?";
                parmlist.add(parms.getString("end") + " 23:59:59");
            }
            if (StringUtils.isNotBlank(parms.getString("grade"))) {
                sql += " and u.grade =?";
                parmlist.add(parms.getString("grade"));
            }
            if (StringUtils.isNotBlank(parms.getString("source"))) {
                sql += " and u.source =?";
                parmlist.add(parms.getString("source"));
            }
        }
        sql += " order by u.id desc";
        return getSession().query2(sql, parmlist, size, pageno);
    }

    public JdataBean getCompany(String id) throws Exception {
        return getSession().getObject("select * from wb_company where id=?", new Object[]{id});
    }

    public JdataBean getCompanyByUid(String userid) throws Exception {
        return getSession().getObject("select * from wb_company where userid=?", new Object[]{userid});
    }

    /**
     * 查询已认证通过的企业信息
     *
     * @param userid
     * @return
     * @throws Exception
     */
    public JdataBean getCompanyedByUid(String userid) throws Exception {
        return getSession().getObject("select * from wb_company where userid=?", new Object[]{userid});
    }

    public void saveCompany(JdataBean bean) throws Exception {
        if (StringUtils.isNotBlank(bean.getString("id"))) {
            Map key = new HashMap();
            key.put("id", bean.getString("id"));
            getSession().update("wb_company", bean.getDataMap(), key);
        } else getSession().save("wb_company", bean.getDataMap());
    }

    public List<JdataBean> querycCompanyQul(String companyid) throws Exception {
        return getSession().query("select * from wb_company_qul where companyid=? order by type", new Object[]{companyid});
    }

    public PageResult queryAuth(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "select c.* from wb_company c where c.state>0";
        List parmlist = new ArrayList();
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("name"))) {
                sql += " and c.name like ?";
                parmlist.add("%" + parms.getString("name") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("type"))) {
                sql += " and c.type = ?";
                parmlist.add(parms.getString("type"));
            }
            if (StringUtils.isNotBlank(parms.getString("state"))) {
                sql += " and c.state = ?";
                parmlist.add(parms.getString("state"));
            }
        }
        sql += " order by c.state,c.submittime desc";
        return getSession().query2(sql, parmlist, size, pageno);
    }

    public void authUpdateState(String companyid, int state, String authcon) throws Exception {
        getSession().update("update wb_company set state=?,authcon=?,authtime=now() where id=?", new Object[]{state, authcon, companyid});
    }

    public JdataBean getUserByOpenId(String openId) throws Exception {
        return getSession().getObject("select * from wbuser where openid=? and state=1", new Object[]{openId});
    }

    public PageResult queryUserFeedBack(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "select f.id,f.userid,f.loginname,f.writetime,f.phone,f.type,f.content,f.photodataid,a.viewpath photoview"
                + " from wb_feedback f left join app_pictures a on a.id=f.photo where 1=1 ";
        List parmlist = new ArrayList();
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("start"))) {
                sql += " and f.writetime >= ?";
                parmlist.add(parms.getString("start"));
            }
            if (StringUtils.isNotBlank(parms.getString("end"))) {
                sql += " and f.writetime <= ?";
                parmlist.add(parms.getString("end") + " 23:59:59");
            }
            if (StringUtils.isNotBlank(parms.getString("type"))) {
                sql += " and f.type=?";
                parmlist.add(parms.getString("type"));
            }
        }
        sql += " order by f.id desc";
        return getSession().query2(sql, parmlist, size, pageno);
    }

    //查看用户反馈
    public JdataBean getFeedBackById(String id) throws Exception {
        return getSession().getObject("select * from wb_feedback where id =?", new Object[]{id});
    }

    public List<JdataBean> queryPicturesDataid(String dataid) throws Exception {
        return getSession().query("select * from app_pictures  where dataid=?", new Object[]{dataid});
    }

    public PageResult queryUserInfo(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "SELECT wu.id,wu.title,wu.pricegrade,wu.usergroup,com.province,com.city,wu.phone,com.`name` from wbuser wu JOIN wb_company com on wu.id=com.userid WHERE wu.state=1 and wu.authstate=2 and com.state=2";
        List parmlist = new ArrayList();
        if (parms != null) {
            String like = parms.getString("like");
            if (StringUtils.isNotBlank(like)) {
                sql += " and (wu.loginname like ? or wu.title like ? or com.name like ? or wu.phone like ?)";
                parmlist.add("%" + like + "%");
                parmlist.add("%" + like + "%");
                parmlist.add("%" + like + "%");
                parmlist.add("%" + like + "%");
            }

            String grade = parms.getString("grade");
            if (StringUtils.isNotBlank(grade)) {
                sql += " and wu.usergroup=?";
                parmlist.add(grade);
            }

            String priceGrade = parms.getString("pricegrade");
            if (StringUtils.isNotBlank(priceGrade)) {
                sql += " and wu.pricegrade=?";
                parmlist.add(priceGrade);
            }

            String province = parms.getString("province");
            if (StringUtils.isNotBlank(province)) {
                sql += " and com.province like ?";
                parmlist.add("%" + province + "%");
            }

            String city = parms.getString("city");
            if (StringUtils.isNotBlank(city)) {
                sql += " and com.city like ?";
                parmlist.add("%" + city + "%");
            }
        }
        return getSession().query2(sql, parmlist, size, pageno);
    }

    public List<JdataBean> getProvince() throws Exception {
        String sql = "SELECT `name` from city WHERE parentid=0";
        return getSession().query(sql, null);
    }

    public List<JdataBean> getCity(String pid) throws Exception {
        String sql = "SELECT ci.`name` from city ci JOIN city ct on ci.parentid=ct.id and ct.`name` LIKE '%" + pid + "%' and ci.`name` != '无'";
        return getSession().query(sql, null);
    }
}
